﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;

namespace DAL
{
    public class PhasesDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoPhases model)
        {
            model.PHA_ID = Guid.NewGuid().ToString();
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [Phases]");
            sql.Append("([PHA_ID],[PHA_NAME],[PHA_CNRTITLE],[PHA_METHOD],[PHA_CONTENT],[PHA_IsShow])");
            sql.Append("VALUES");
            sql.Append("(@PHA_ID,@PHA_NAME,@PHA_CNRTITLE,@PHA_METHOD,@PHA_CONTENT,@PHA_IsShow)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoPhases model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Phases] SET ");
            sql.Append("PHA_NAME=@PHA_NAME");
            sql.Append(",PHA_CNRTITLE=@PHA_CNRTITLE");
            sql.Append(",PHA_METHOD=@PHA_METHOD");
            sql.Append(",PHA_CONTENT=@PHA_CONTENT");
            sql.Append(",PHA_IsShow=@PHA_IsShow");
            sql.Append(" WHERE ");
            sql.Append("PHA_ID=@PHA_ID");

            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string pHA_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Phases] SET");
            sql.Append(" PHA_IsShow='false'");
            sql.Append(" WHERE ");
            sql.Append("PHA_ID=@PHA_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PHA_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = pHA_ID;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }

        /// <summary>
        /// 获取一条记录
        /// </summary>
        /// <returns></returns>
        public BoPhases GetModel(string pHA_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PHA_ID,PHA_NAME,PHA_CNRTITLE,PHA_METHOD,PHA_CONTENT,PHA_IsShow FROM Phases");
            sql.Append(" WHERE ");
            sql.Append("PHA_ID=@PHA_ID AND PHA_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PHA_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = pHA_ID;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoPhases> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        public IList<BoPhases> GetAllPhases()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PHA_ID,PHA_NAME,PHA_CNRTITLE,PHA_METHOD,PHA_CONTENT,PHA_IsShow FROM Phases ");
            sql.Append("WHERE PHA_IsShow='true'");
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            IList<BoPhases> list = GetList(table);
            return list;
        }

        private IList<BoPhases> GetList(DataTable table)
        {
            List<BoPhases> list = new List<BoPhases>();
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoPhases model = new BoPhases();
                    model.PHA_ID = table.Rows[i]["PHA_ID"]==System .DBNull .Value ?"":(string)table.Rows[i]["PHA_ID"];
                    model.PHA_NAME = table.Rows[i]["PHA_NAME"]==System .DBNull .Value ?"":(string)table.Rows[i]["PHA_NAME"];
                    model.PHA_CNRTITLE = table.Rows[i]["PHA_CNRTITLE"]==System .DBNull .Value ?"":(string)table.Rows[i]["PHA_CNRTITLE"];
                    model.PHA_METHOD = table.Rows[i]["PHA_METHOD"]==System .DBNull .Value ?"":(string)table.Rows[i]["PHA_METHOD"];
                    model.PHA_CONTENT = table.Rows[i]["PHA_CONTENT"]==System .DBNull.Value ?"":(string)table.Rows[i]["PHA_CONTENT"];
                    model.PHA_IsShow = table.Rows[i]["PHA_IsShow"] == System.DBNull.Value ? true : (bool)table.Rows[i]["PHA_IsShow"];
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoPhases model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@PHA_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@PHA_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@PHA_CNRTITLE",SqlDbType.NVarChar,50),
                new SqlParameter("@PHA_METHOD",SqlDbType.NVarChar,50),
                new SqlParameter("@PHA_CONTENT",SqlDbType.Text,16),
                new SqlParameter ("@PHA_IsShow",SqlDbType.Bit ,2),
            };
            parameters[0].Value = model.PHA_ID;
            parameters[1].Value = model.PHA_NAME;
            parameters[2].Value = model.PHA_CNRTITLE;
            parameters[3].Value = model.PHA_METHOD;
            parameters[4].Value = model.PHA_CONTENT;
            parameters[5].Value = model.PHA_IsShow;
            return parameters;
        }

        /// <summary>
        /// 得到分页的数据列表
        /// </summary>
        /// <param name="orderCon">排序条件</param>
        /// <param name="sqlCon">查询条件</param>
        /// <param name="pagesize">每页显示大小</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="records">返回查询到的记录</param>
        /// <returns>返回对象列表</returns>
        public IList<BoPhases> getPagePhases(string sqlCon, string orderCon, int pagesize, int pageIndex, out int records)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT PHA_ID,PHA_NAME,PHA_CNRTITLE,PHA_METHOD,PHA_CONTENT, PHA_IsShow FROM Phases where 1=1 and PHA_IsShow=1 ");
            if (!string.IsNullOrEmpty(sqlCon))
            {
                sql.Append("and ");
                sql.Append(sqlCon);
            }
            DataTable table = dbhelper.ExecuteSp_Page(sql.ToString(), orderCon, pagesize, pageIndex, out records);
            IList<BoPhases> list = GetList(table);
            return list != null && list.Count > 0 ? list : null;
        }
    }
}